const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcryptjs');
const path = require('path');

const dbPath = path.join(__dirname, 'rongcheng.db');
const db = new sqlite3.Database(dbPath);

const initDatabase = async () => {
  return new Promise((resolve, reject) => {
    db.serialize(async () => {
      try {
        // 创建用户表
        db.run(`
          CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'user',
            phone TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
          )
        `);

        // 创建账单表
        db.run(`
          CREATE TABLE IF NOT EXISTS bills (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            category TEXT NOT NULL,
            description TEXT,
            date DATE NOT NULL,
            created_by INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by) REFERENCES users (id)
          )
        `);

        // 插入管理员账号
        const adminUsername = 'szllflxy';
        const adminPassword = 'rongcheng16888A';
        const hashedPassword = await bcrypt.hash(adminPassword, 10);

        db.run(
          `INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)`,
          [adminUsername, hashedPassword, 'admin'],
          (err) => {
            if (err) {
              console.error('创建管理员账号失败:', err);
            } else {
              console.log('管理员账号已创建/已存在');
            }
          }
        );

        console.log('数据库初始化完成');
        resolve();
      } catch (error) {
        console.error('数据库初始化失败:', error);
        reject(error);
      }
    });
  });
};

module.exports = { db, initDatabase };
